﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Utility.ConnectionManager;

namespace AccessControl.Data
{
    public enum ErrorModeConstants{
        Ignore,
        ThrowException
    }

    public class Connection{

        private SqlConnection DataCnn;

        //public Func<DataTable> OnDataReady { get; set; }
        //private DaConnection instance; 
        //public DaConnection GetInstance{
        //    get{
        //        return instance ?? (instance = new DaConnection());
        //    }
        //}

        private static string cnnString;
        public static string CnnString{
            get{
                return cnnString ?? (cnnString = new ConnectionStringManager("AccessControl").GetConnectionString());
            }
        }

        public Connection() {
            DataCnn = new SqlConnection(CnnString);

        }

        public Connection(bool open) {
            DataCnn = new SqlConnection("Connect Timeout=30;" + CnnString);
            if (open) Connect();
        }

        public SqlConnection CurrentConnection {
            get { return DataCnn; }
        }

        public SqlConnection Connect() {
            DataCnn.Open();
            return DataCnn;
        }
        public void Disconnect() {
            if (DataCnn.State != ConnectionState.Closed) DataCnn.Close();
        }

        public static object ExecuteScalar(SqlCommand SQLCmd, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            var Cnn = new Connection();
            try {
                SQLCmd.Connection = Cnn.Connect();
                return SQLCmd.ExecuteScalar();
            } catch (Exception ex) {
                if (errorMode== ErrorModeConstants.ThrowException)
                    throw new Exception(ex.Message);
                return null;
            } finally {
                Cnn.Disconnect();
            }
        }
        public static object ExecuteScalar(string CommandText, CommandType CommandType, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            var Cnn = new Connection();
            try {
                var SQLCmd = new SqlCommand(CommandText, Cnn.Connect());
                SQLCmd.CommandType = CommandType;
                return SQLCmd.ExecuteScalar();
            } catch (Exception ex) {
                if (errorMode == ErrorModeConstants.ThrowException)
                    throw new Exception(ex.Message);
                return null;
            } finally {
                Cnn.Disconnect();
            }
        }

        public static int ExecuteNonQuery(SqlCommand SQLCmd, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException) {
            var Cnn = new Connection();
            try {
                SQLCmd.Connection = Cnn.Connect();
                return SQLCmd.ExecuteNonQuery();
            } catch (Exception ex) {
                if (errorMode == ErrorModeConstants.ThrowException)
                    throw new Exception(ex.Message);
                return 0;
            }
            finally
            {
                Cnn.Disconnect();
            }
        }
        public static int ExecuteNonQuery(string CommandText, CommandType CommandType, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException) {
            var Cnn = new Connection();
            try {
                var SQLCmd = new SqlCommand(CommandText, Cnn.Connect());
                SQLCmd.CommandType = CommandType;
                return SQLCmd.ExecuteNonQuery();
            } catch (Exception ex) {
                if (errorMode == ErrorModeConstants.ThrowException)
                    throw new Exception(ex.Message);
                return 0;
            }
            finally
            {
                Cnn.Disconnect();
            }
        }
        public static DataTable GetDataTable(string CommandText, CommandType type, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException) {
            var tbl = new DataTable();
            Fill(ref tbl, CommandText, type, null, errorMode);
            return tbl;
        }

        public static void Fill(ref DataTable tbl, string CommandText, CommandType type, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException) {
            Fill(ref tbl, CommandText, type, null,errorMode);
        }
        public static DataTable GetDataTable(string CommandText, CommandType type, string KeyColumn, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException) {
            var tbl = new DataTable();
            Fill(ref tbl, CommandText, type,KeyColumn,errorMode);
            return tbl;
        }
        public static void Fill(ref DataTable tbl, string CommandText, CommandType type, string KeyColumn, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            var SQLCmd = new SqlCommand(CommandText);
            SQLCmd.CommandType = type;
            Fill(ref tbl,SQLCmd, KeyColumn,errorMode);
        }


        public static DataTable GetDataTable(SqlCommand sqlcmd, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            var tbl = new DataTable();
            Fill(ref tbl, sqlcmd, null, false, errorMode);
            return tbl;
        }
        public static void Fill(ref DataTable tbl, SqlCommand sqlcmd, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            Fill(ref tbl,sqlcmd, null,errorMode);
        }
        public static DataTable GetDataTable(SqlCommand sqlcmd, string KeyColumn, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            var tbl = new DataTable();
            Fill(ref tbl, sqlcmd, KeyColumn, true, errorMode);
            return tbl;
        }
        public static void Fill(ref DataTable tbl, SqlCommand sqlcmd, string KeyColumn, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            Fill(ref tbl, sqlcmd, KeyColumn, true,errorMode);
        }
        public static DataTable GetDataTable(SqlCommand sqlcmd, string KeyColumn, bool IsAutoNumber, ErrorModeConstants errorMode = ErrorModeConstants.ThrowException)
        {
            var tbl = new DataTable();
            Fill(ref tbl, sqlcmd, KeyColumn, IsAutoNumber,errorMode);
            return tbl;
        }

        public static void Fill(ref DataTable tbl, SqlCommand sqlcmd, string KeyColumn, bool IsAutoNumber, ErrorModeConstants errorMode= ErrorModeConstants.ThrowException)
        {
            if (tbl == null) tbl = new DataTable();
            
            var Conn = new Connection();
            try {
                sqlcmd.Connection = Conn.Connect();

                var adp = new SqlDataAdapter(sqlcmd);
                tbl.AcceptChanges();
                adp.Fill(tbl);
                tbl.AcceptChanges();

                if (KeyColumn != null && tbl.Constraints.Count==0) {
                    tbl.Constraints.Add(KeyColumn, tbl.Columns[KeyColumn], true);
                    if (IsAutoNumber) {
                        var Key = tbl.Columns[KeyColumn];
                        if(Key.DataType != typeof(String)){
                        Key.AutoIncrement = true;
                        Key.AutoIncrementSeed = int.MinValue;
                        }
                    }
                }

            } catch (Exception ex) {
                if (errorMode == ErrorModeConstants.ThrowException)
                    throw new Exception(ex.Message);
            }
            finally
            {
                Conn.Disconnect();
            }
        }


    }

}
